package com.gitee.taotaojs.util.excel;

import com.gitee.taotaojs.exception.MyInnerException;
import com.gitee.taotaojs.exception.MyInnerExceptionList;
import com.gitee.taotaojs.util.CollectionUtil;
import com.gitee.taotaojs.util.ConstantUtil;
import com.gitee.taotaojs.util.StringUtil;
import com.gitee.taotaojs.util.number.BigDecimalUtil;
import com.gitee.taotaojs.util.number.NumberFormatUtil;
import com.gitee.taotaojs.util.reflect.EntityUtil;
import com.gitee.taotaojs.util.reflect.ReflectUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @author TaoTaojs
 * @date 2020/7/6 3:25 PM
 * <p>Description</p>
 * <h1>excel读取工具类</h1>
 * <h2>简介</h2>
 * excel读取工具类
 * <h3>主要功能：</h3>
 * <ol>
 *     <li>Excel表格与实体类直接映射（使用ExcelHead类）</li>
 *     <li>将表中表头至表数据头之间的表级字段映射为Map对象（属于线程安全，效率降低）</li>
 *     <li>出现错误时，返回所有错误的文字表述</li>
 * </ol>
 * <h3>使用方法</h3>
 * 直接传入
 * 关联类:
 * 参考链接：https://blog.csdn.net/qq_39623859/article/details/80881906
 * History: <br/>
 * <author>        <time>                      <version>          <desc>
 * TaoTaojs        2020/7/6 3:25 PM                V1.0
 */
public final class ExcelUtil {

    private ExcelUtil(){}

    /** 错误列表 */
    protected static final MyInnerExceptionList ERROR = new MyInnerExceptionList();

    /**
     * 直接获取到WorkBook对象（兼容新老版本）
     * @param in         excel流
     * @param fileName   文件名
     * @return 一个WorkBook对象
     * @throws Exception
     */
    public static Workbook getWorkBook(InputStream in, String fileName) {
        //是否EXCEL文件
        checkFile(fileName);
        //兼容新老版本
        return getWorkBoot(in, fileName);
    }

    /**
     * Excel表头对应Entity属性 解析封装javabean
     * @param clazz      类
     * @param in         excel流
     * @param fileName   文件名
     * @param excelHeads excel表头与entity属性对应关系
     * @param <T> <工作表名称, 映射实体类列表>
     * @param firstHead  数据表头首个表头文字
     * @return 完整的excel文件数据，以sheetName为key，其他数据为value的map格式数据
     */
    public static <T> Map<String, ExcelTableBean<T>> readExcelToEntity(Class<T> clazz, InputStream in, String fileName, List<AbstractExcelHead> excelHeads, String firstHead) {
        Workbook workbook = getWorkBook(in, fileName);
        //解析Excel
        return readExcelToEntity(clazz, workbook, excelHeads, firstHead);
    }
    @SuppressWarnings("checkstyle:MissingJavadocMethod")
    public static <T> Map<String, ExcelTableBean<T>> readExcelToEntity(Class<T> classzz, Workbook workbook, List<AbstractExcelHead> excelHeads, String firstHead) {
        //解析Excel
        return readExcel(classzz, workbook, excelHeads, firstHead);
    }
    @SuppressWarnings("checkstyle:MissingJavadocMethod")
    public static<T> Map<String, ExcelTableBean<T>> readExcelToEntity(Class<T> clazz, String filePath, String firstHead, AbstractExcelHead... excelHeads){
        File file = new File(filePath);
        List<AbstractExcelHead> excelHeadList = Arrays.asList(excelHeads);
        try(FileInputStream in = new FileInputStream(file)) {
            return readExcelToEntity(clazz, in, file.getName(), excelHeadList, firstHead);
        } catch (IOException e) {
            throw new MyInnerException(e);
        }
    }


    /**
     *
     * @param clazz 映射的目标对象
     * @param workbook 表格数据
     * @param excelHeads 属性与表头的映射关系
     * @param firstHead 第一个表头的列头名称
     * @param <T> 对象泛型
     * @return 表格数据
     */
    private static <T> Map<String, ExcelTableBean<T>> readExcel(Class<T> clazz, Workbook workbook, List<AbstractExcelHead> excelHeads, String firstHead){
        ERROR.clear();
        Map<String, ExcelTableBean<T>> beansMap = new HashMap<>(ConstantUtil.TWO);

        int sheetNum = workbook.getNumberOfSheets();
        for(int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++){
            //获取表级数据
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            //获得表名
            String sheetName=sheet.getSheetName();
            ExcelTableBean<T> excel = readTable(sheet, firstHead, clazz, excelHeads);
            if(excel != null) {
                beansMap.put(sheetName, excel);
            }
        }

        return beansMap;
    }

    /**
     * 获取sheet数据
     * @param sheet sheet对象
     * @param firstHead 数据表头第一个表头字符串
     * @param clazz 泛型对象类型
     * @param excelHeads 表头映射关系
     * @param <T> 对象泛型
     * @return 表格数据
     */
    private static<T> ExcelTableBean<T> readTable(Sheet sheet, String firstHead, Class<T> clazz, List<AbstractExcelHead> excelHeads){
        ExcelTableBean<T> tableBean = new ExcelTableBean<>();
        tableBean.<T>setDataList(new ArrayList<T>());


        int firstRowNum = getTableHeaderLocation(sheet, firstHead);
        int lastRowNum = sheet.getLastRowNum();

        //获得表头列数据
        Row head = sheet.getRow(firstRowNum);
        if(head == null){
            return null;
        }

        //+1是为了排除表头数据
        for(int rowIndex = firstRowNum+1; rowIndex <= lastRowNum; rowIndex++){
            Row dataRow = sheet.getRow(rowIndex);
            //整行空值判断
            //volidateRowRequired(dataRow)
            if(dataRow == null){
                continue;
            }
            T t = readRow(dataRow, head, clazz, excelHeads);
            if(t != null && !EntityUtil.isEmpty(t)){
                tableBean.getDataList().add(t);
            }
        }
        tableBean.setTableName(sheet.getSheetName());
        tableBean.setDataMap(splitHead(sheet, firstRowNum));

        return tableBean;
    }

    /**
     * 读取行数据
     * @param dataRow 行对象
     * @param head 表头行
     * @param clazz 泛型对象类型
     * @param excelHeads 表头映射关系
     * @param <T> 对象泛型
     * @return 实体对象，映射完成之后的
     */
    private static<T> T readRow(Row dataRow, Row head, Class<T> clazz, List<AbstractExcelHead> excelHeads){
        //获得数据行中列的区域
        short firstCellNum = head.getFirstCellNum();
        short lastCellNum = head.getLastCellNum();
        T instance = EntityUtil.getInstance(clazz);

        for(int cellIndex = firstCellNum; cellIndex < lastCellNum; cellIndex++){
            Cell headCell = head.getCell(cellIndex);
            Cell dataCell = dataRow.getCell(cellIndex);
            if(headCell != null && StringUtil.isNotBlank(SheetUtils.getMergedRegionValue(headCell)) && dataCell != null){
                String value = null;
                //如果单元格类型是数字
                if(dataCell.getCellTypeEnum().equals(CellType.NUMERIC)) {
                    Double index = dataCell.getNumericCellValue();
                    value = BigDecimal.valueOf(index).stripTrailingZeros().toPlainString();
                    value = BigDecimalUtil.format(index, NumberFormatUtil.getScientificNotation(value));
                    if (isPercent(dataCell)) {
                        //处理百分号
                        value = BigDecimalUtil.format(BigDecimalUtil.multiply(value, "100"));
                    } else if (isPermillage(dataCell)) {
                        //处理千分号
                        value = BigDecimalUtil.format(BigDecimalUtil.multiply(value, "1000").toString());
                    }
                } else {
                    //获取单元格值，并对特殊格式数据进行处理
                    value = SheetUtils.getMergedRegionValue(dataCell);
                }

                //如果得到的结果不为null
                if(StringUtil.isNotBlank(value)) {
                    setInstanceProByExcelHead(instance, clazz, value, dataCell, headCell, excelHeads);
                }
            }
        }

        return instance;
    }

    /**
     * 找到对应表头映射属性字段，并且赋值，如果找不到，就写入到data属性中，data属性类型为Map<String, String>
     * @param instance 实体对象
     * @param clazz 实体对象类型
     * @param value 表格值
     * @param dataCell 数据单元格
     * @param headCell 表头单元格
     * @param excelHeads 表头映射关系
     * @param <T> 实体对象泛型
     * @return 添加完属性之后的实体对象
     */
    private static<T> T setInstanceProByExcelHead(T instance, Class<T> clazz, String value, Cell dataCell, Cell headCell, List<AbstractExcelHead> excelHeads){
        AbstractExcelHead excelHead = getHeaderMapper(headCell, excelHeads);
        if(excelHead != null){
            if(volidateValueRequired(excelHead, dataCell)){
                Field field = EntityUtil.getField(clazz, excelHead.getEntityName());
                ReflectUtil.invokeSet(instance, excelHead.getEntityName(), SheetUtils.convertType(field.getType(), value));
            }

        } else {
            Map<String, String> linkMap = (LinkedHashMap<String, String>) ReflectUtil.invokeGet(instance, "data");
            if(linkMap == null){
                linkMap = new LinkedHashMap<>();
                ReflectUtil.invokeSet(instance, "data", linkMap, Map.class);
            }
            linkMap.put(SheetUtils.getMergedRegionValue(headCell), value);
        }
        return instance;
    }

    /**
     * 获取表头与表头映射对象的关联关系
     * @param head 表头
     * @param excelHeads 表头映射对象集合
     * @return 映射关系对象
     */
    private static AbstractExcelHead getHeaderMapper(Cell head, List<AbstractExcelHead> excelHeads){
        for (AbstractExcelHead excelHead : excelHeads) {
            if (excelHead.getExcelName().equals(SheetUtils.getMergedRegionValue(head))) {
                return excelHead;
            }
        }
        return null;
    }

    /**
     * 获取表头行数位置
     * @param sheet 表格对象
     * @param firstHead 数据表头首个表头字符串
     * @return 表头行位置
     */
    private static int getTableHeaderLocation(Sheet sheet, String firstHead){
        int firstRowNum = 0;
        if(StringUtil.isNotBlank(firstHead)){
            for (int rowIndex = firstRowNum; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                Row dataRow = sheet.getRow(rowIndex);
                if (dataRow == null) {
                    continue;
                }
                if(dataRow.getCell(0) != null) {
                    dataRow.getCell(0).setCellType(CellType.STRING);
                    String cellValue = SheetUtils.getMergedRegionValue(dataRow.getCell(0));
                    if (cellValue.equals(firstHead)) {
                        firstRowNum = rowIndex;
                        return firstRowNum;
                    }
                }
            }
        }

        throw new MyInnerException("导入的模板不正确，表格首列必须为【" + firstHead + "】请检查");
    }

    /**
     * 判断单元格对象是否为百分号
     * @param cell 单元格对象
     * @return 是百分号返回true
     */
    public static boolean isPercent(Cell cell){
        String str = cell.getCellStyle().getDataFormatString();
        cell.setCellType(CellType.STRING);
        String value = SheetUtils.getMergedRegionValue(cell);
        //当单元格格式为0.00%号，并且值为纯数字时，才判断这个单元格需要使用百分比的方式处理
        return str.indexOf(ConstantUtil.PERCENT) != -ConstantUtil.ONE && NumberFormatUtil.isNumber(value);
    }

    /**
     * 判断单元格对象是否为千分号
     * @param cell 单元格对象
     * @return 是千分号返回true
     */
    public static boolean isPermillage(Cell cell){
        String str = cell.getCellStyle().getDataFormatString();
        cell.setCellType(CellType.STRING);
        String value = SheetUtils.getMergedRegionValue(cell);
        //当单元格格式为0.00%号，并且值为纯数字时，才判断这个单元格需要使用百分比的方式处理
        return str.indexOf(ConstantUtil.PERMILLAGE) != -ConstantUtil.ONE && NumberFormatUtil.isNumber(value);
    }


    /**
     * 判断单元格是否符合表头映射对象的限制
     * @param excelHead 映射关系对象
     * @param dataCell 数据单元格
     * @return 是否符合限制，符合返回true
     */
    private static boolean volidateValueRequired(AbstractExcelHead excelHead, Cell dataCell) {
        boolean blankFlag = StringUtil.isBlank(SheetUtils.getMergedRegionValue(dataCell));
        if (excelHead != null && excelHead.isRequired() && blankFlag) {
            ERROR.add(new MyInnerException("《"+dataCell.getSheet().getSheetName()+"》第"+(dataCell.getRowIndex())+"行:\""+excelHead.getExcelName() + "\"不能为空！; \r\n</br>"));
            return false;
        }
        return true;
    }
    /**
     * 空行校验
     * @param row 行
     * @return 空行返回true
     */
    protected static boolean volidateRowRequired(Row row) {
        int firstCellNum = row.getFirstCellNum();
        int lastCellNum = row.getLastCellNum();
        for(int index=firstCellNum; index<lastCellNum; index++){
            Cell cell = row.getCell(index);
            if(cell == null) {
                continue;
            }
            cell.setCellType(CellType.STRING);
            if(StringUtil.isNotBlank(SheetUtils.getMergedRegionValue(cell))){
                return false;
            }
        }
        return true;
    }

    /**
     * 将表中表头至表数据头之间的表级字段映射为Map对象
     * @param sheet 表对象
     * @param headNum 数据表头第一列内容
     * @return 表头映射map的对象
     */
    private static Map<String, String> splitHead(Sheet sheet, int headNum){
        Map<String, String> mapTemp = new HashMap<>(ConstantUtil.TWO);
        int index = 0;

        for (int i = sheet.getFirstRowNum(); i < headNum; i++) {
            Row row = sheet.getRow(i);
            if(row == null) {
                continue;
            }
            //标题暂存
            Cell upCell = null;
            //遍历整行
            Iterator<Cell> iterator = row.cellIterator();
            while (iterator.hasNext()){
                Cell cell = iterator.next();
                //如果此时表头没有暂存这，就将这个暂时设置成表头
                if(upCell == null){
                    upCell = cell;
                    continue;
                }
                //判断两个单元格是否是合并单元格
                if(!SheetUtils.isEqualMergedRegion(sheet, cell, upCell)) {
                    cell.setCellType(CellType.STRING);
                    //如果不是就写入map中，并且将titleTemp设置为null
                    mapTemp.put(SheetUtils.getMergedRegionValue(upCell), SheetUtils.getMergedRegionValue(cell));
                    upCell = null;
                }

            }
            if(upCell != null){
                mapTemp.put("title" + (++index), SheetUtils.getMergedRegionValue(upCell));
            }
        }
        return mapTemp;
    }

    /**
     * 校验是否是Excel文件
     * @param fileName 文件名称
     */
    private static void checkFile(String fileName) {
        boolean suffixFlag = !(fileName.endsWith(ConstantUtil.XLSX) || fileName.endsWith(ConstantUtil.XLS));
        if (!StringUtil.isBlank(fileName) && suffixFlag) {
            throw new MyInnerException("不是Excel文件！");
        }
    }

    /**
     * 兼容新老版Excel
     * @param in 文件流
     * @param fileName 文件名称
     * @return workbook对象
     */
    private static Workbook getWorkBoot(InputStream in, String fileName) {
        try {
            if (fileName.endsWith(ConstantUtil.XLSX)) {
                return new XSSFWorkbook(in);
            } else {
                return new HSSFWorkbook(in);
            }
        } catch (IOException e) {
            throw new MyInnerException();
        }
    }

    /**
     * 将Map对象整理成ExcelHead对象列表
     * @param map map关系
     * @return 整理成映射对象集合
     */
    public static List<AbstractExcelHead> getHeadList(Map<String, String> map){
        List<AbstractExcelHead> excelHeads = new ArrayList<>();
        for (Map.Entry<String, String> entry : map.entrySet()) {
            Boolean flag = false;
            String proName = entry.getKey();
            String value = map.get(entry.getKey());
            if(entry.getKey().indexOf(ConstantUtil.ASTERISK) >= 0){
                flag = true;
                proName = entry.getKey().substring(1);
            }
            if(!StringUtil.isBlank(value)){
                List<String> valueList = CollectionUtil.getString(value);
                for (String str : valueList) {
                    excelHeads.add(ExcelHead.builder().excelName(str).entityName(proName).required(flag).build());
                }
            }
        }
        return excelHeads;
    }

    /**
     * 将Map对象整理成ExcelHead对象列表
     * @param map 映射关系
     * @return 整理成映射对象集合
     */
    public static List<AbstractExcelHead> getHeadPluralList(Map<String, String> map){
        List<AbstractExcelHead> excelHeads = new ArrayList<>();
        for (Map.Entry<String, String> stringEntry : map.entrySet()) {
            Boolean flag = false;
            String proName = stringEntry.getKey();
            String value = map.get(stringEntry.getKey());
            if(stringEntry.getKey().indexOf(ConstantUtil.ASTERISK) >= 0){
                flag = true;
                proName = stringEntry.getKey().substring(1);
            }
            if(!StringUtil.isEmpty(value)){
                excelHeads.add(ExcelPluralHead.builder().excelName(new ArrayList<>(Arrays.asList(value.split(",")))).entityName(proName).required(flag).build());
            }
        }
        return excelHeads;
    }


}
